/**
 * 初始化数据库表
 * @param {object} connection - MySQL 数据库连接对象
 * @returns {Promise<void>}
 */
function initDatabase(connection) {
  const queries = [
    // macau
    `CREATE TABLE IF NOT EXISTS macau_history_results (
        id INT AUTO_INCREMENT PRIMARY KEY,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        red_continuous_limit_2_group_ids JSON, -- 预测红色连续不超过2次的group id数组
        green_rebound_red_non_continuous_group_ids JSON, -- 预测绿色回弹，红色不连续的group id数组
        green_continuous_group_ids JSON, -- 预测绿色连续的group id数组
        lucky_array JSON,
        kill_array JSON,
        accuracy INT
      )`,
    `CREATE INDEX idx_macau_history_results_time ON macau_history_results(time(10))`,

    `CREATE TABLE IF NOT EXISTS macau_current_group_infos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES macau_history_results(id)
        )`,
    `CREATE INDEX idx_macau_current_group_infos_history_result_id ON macau_current_group_infos(history_result_id)`,
    `CREATE INDEX idx_macau_current_group_infos_group_name ON macau_current_group_infos(group_name)`,

    `CREATE TABLE IF NOT EXISTS macau_group_accuracy (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        accuracy INT NOT NULL,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES macau_history_results(id)
        )`,
    `CREATE INDEX idx_macau_group_accuracy_history_result_id ON macau_group_accuracy(history_result_id)`,
    `CREATE INDEX idx_macau_group_accuracy_group_name ON macau_group_accuracy(group_name)`,
    `CREATE INDEX idx_macau_group_accuracy_group_name_time ON macau_group_accuracy (group_name, time)`,

    `CREATE TABLE IF NOT EXISTS macau_daily_group_accuracy_summary (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT NOT NULL,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        accuracy INT NOT NULL,
        accuracy_array JSON,
        correct_count INT,
        incorrect_count INT,
        accuracy_rate FLOAT,
        max_consecutive_correct INT,
        max_consecutive_incorrect INT,
        consecutive_correct_segments INT,
        consecutive_incorrect_segments INT,
        avg_consecutive_correct FLOAT,
        avg_consecutive_incorrect FLOAT,
        current_consecutive_correct INT,
        current_consecutive_incorrect INT,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES macau_history_results(id)
        )`,
    `CREATE INDEX idx_macau_daily_summary_history_result_id ON macau_daily_group_accuracy_summary(history_result_id)`,
    `CREATE INDEX idx_macau_daily_summary_group_name ON macau_daily_group_accuracy_summary(group_name)`,

    // hongkong

    `CREATE TABLE IF NOT EXISTS hongkong_history_results (
        id INT AUTO_INCREMENT PRIMARY KEY,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        red_continuous_limit_2_group_ids JSON, -- 预测红色连续不超过2次的group id数组
        green_rebound_red_non_continuous_group_ids JSON, -- 预测绿色回弹，红色不连续的group id数组
        green_continuous_group_ids JSON, -- 预测绿色连续的group id数组
        lucky_array JSON,
        kill_array JSON,
        accuracy INT
      )`,
    `CREATE INDEX idx_hongkong_history_results_time ON hongkong_history_results(time(10))`,

    `CREATE TABLE IF NOT EXISTS hongkong_current_group_infos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES hongkong_history_results(id)
        )`,
    `CREATE INDEX idx_hongkong_current_group_infos_history_result_id ON hongkong_current_group_infos(history_result_id)`,
    `CREATE INDEX idx_hongkong_current_group_infos_group_name ON hongkong_current_group_infos(group_name)`,

    `CREATE TABLE IF NOT EXISTS hongkong_group_accuracy (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        accuracy INT NOT NULL,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES hongkong_history_results(id)
        )`,
    `CREATE INDEX idx_hongkong_group_accuracy_history_result_id ON hongkong_group_accuracy(history_result_id)`,
    `CREATE INDEX idx_hongkong_group_accuracy_group_name ON hongkong_group_accuracy(group_name)`,
    `CREATE INDEX idx_hongkong_group_accuracy_group_name_time ON hongkong_group_accuracy (group_name, time)`,

    `CREATE TABLE IF NOT EXISTS hongkong_daily_group_accuracy_summary (
        id INT AUTO_INCREMENT PRIMARY KEY,
        history_result_id INT NOT NULL,
        time VARCHAR(255) NOT NULL,
        number VARCHAR(255) NOT NULL,
        group_name VARCHAR(255) NOT NULL,
        group_type VARCHAR(255) NOT NULL,
        sub_index INT NOT NULL,
        sub_array JSON,
        accuracy INT NOT NULL,
        accuracy_array JSON,
        correct_count INT,
        incorrect_count INT,
        accuracy_rate FLOAT,
        max_consecutive_correct INT,
        max_consecutive_incorrect INT,
        consecutive_correct_segments INT,
        consecutive_incorrect_segments INT,
        avg_consecutive_correct FLOAT,
        avg_consecutive_incorrect FLOAT,
        current_consecutive_correct INT,
        current_consecutive_incorrect INT,
        UNIQUE(history_result_id, group_name),
        FOREIGN KEY (history_result_id) REFERENCES hongkong_history_results(id)
        )`,
    `CREATE INDEX idx_hongkong_daily_summary_history_result_id ON hongkong_daily_group_accuracy_summary(history_result_id)`,
    `CREATE INDEX idx_hongkong_daily_summary_group_name ON hongkong_daily_group_accuracy_summary(group_name)`
  ]

  // 将查询数组转换为 Promise
  const queryPromises = queries.map((query) => {
    return new Promise((resolve, reject) => {
      connection.query(query, (error, results) => {
        if (error) {
          console.error('执行SQL语句时出错:', error)
          reject(error)
        } else {
          console.log('SQL语句执行成功')
          resolve(results)
        }
      })
    })
  })

  // 执行所有查询
  return Promise.all(queryPromises)
    .then(() => {
      console.log('所有SQL语句已成功执行')
    })
    .catch((error) => {
      console.error('在执行过程中发生错误:', error)
      throw error // 抛出错误以便外部处理
    })
}

// todo cyan:
//  daily_group_accuracy_summary 需要一个 history_result_id 和 id 联合索引

async function safeInitDatabase(connection) {
  // 查询数据库中的所有表
  const showTablesQuery = 'SHOW TABLES'

  return new Promise((resolve, reject) => {
    console.log('showTablesQuery', showTablesQuery)
    connection.query(showTablesQuery, async (error, results) => {
      console.log('results', results)
      if (error) {
        console.error('查询数据库表时出错:', error)
        reject(error)
      } else {
        // 检查结果数量
        if (results.length > 1) {
          console.log('数据库中已存在多张表，跳过初始化操作。')
          resolve('已存在表')
        } else {
          console.log('空数据库，开始初始化...')
          try {
            await initDatabase(connection) // 调用原始的初始化函数
            // console.log('数据库初始化完成。')
            resolve('建表完成')
          } catch (initError) {
            console.error('数据库初始化失败:', initError)
            reject(initError)
          }
        }
      }
    })
  })
}

module.exports = {
  initDatabase,
  safeInitDatabase
}
